package com.rocksea.app.db.history;

import android.content.Context;



import android.database.Cursor;
import android.os.Environment;

import android.database.sqlite.SQLiteDatabase;
import com.litesuits.orm.LiteOrm;
import com.litesuits.orm.db.DataBaseConfig;
import com.litesuits.orm.db.assit.QueryBuilder;
import com.litesuits.orm.db.assit.SQLiteHelper;
import com.litesuits.orm.db.assit.WhereBuilder;
import com.litesuits.orm.db.model.ColumnsValue;
import com.litesuits.orm.db.model.ConflictAlgorithm;
import com.rocksea.app.db.DbCenter;
import com.rocksea.app.entry.CommitBean;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.TreeMap;

/**
 * Created by 吴志华 on 2017/7/28.
 *
 */

public class HistoryDbHelper implements SQLiteHelper.OnUpdateListener {

    private static LiteOrm mHistoryDb;
    private static HistoryDbHelper mHistoryDbHelper;

    private HistoryDbHelper(Context context) {

        DataBaseConfig config = new DataBaseConfig(context);
        config.dbName = Environment.getExternalStorageDirectory().getPath()+"/RailDatabase/db/HistoryTable.db";
        config.dbVersion = 1;
        mHistoryDb = LiteOrm.newCascadeInstance(config);
        //mHistoryDb.setDebugged(true);
        HistoryTable historyTable = new HistoryTable();
        mHistoryDb.insert(historyTable);
        mHistoryDb.delete(historyTable);
    }

    public static HistoryDbHelper getInstance(Context context)
    {
        if (mHistoryDbHelper==null)
        {
            synchronized (HistoryDbHelper.class)
            {
                mHistoryDbHelper = new HistoryDbHelper(context);
            }
        }
        return mHistoryDbHelper;
    }

    /**
     * 删除历史记录表中的一条数据
     * @param baseInfoId 指定的UUID
     *
     * */
    public int delete(String baseInfoId)
    {
        WhereBuilder whereBuilder = new WhereBuilder(HistoryTable.class);
        whereBuilder.where("BaseInfoId LIKE ?",baseInfoId);

        return mHistoryDb.delete(whereBuilder);
    }

    /**
     * 查询数据库
     * @param start 开始时间
     * @param end 结束时间
     * @return 数组集合
     *
     * */
    public ArrayList<HistoryTable> queryByTime(long start,long end)
    {
        QueryBuilder<HistoryTable> queryBuilder =
                new QueryBuilder<>(HistoryTable.class);
        queryBuilder.whereGreaterThan("CreatTime",start);
        queryBuilder.whereAppendAnd();
        queryBuilder.whereLessThan("CreatTime",end);
        ArrayList<HistoryTable> historyTables = mHistoryDb.query(queryBuilder);
        /*for (HistoryTable historyTable : historyTables)
        {
            if (historyTable.Type == HistoryTable.TEST_DY)
            {
                if (!DbCenter.mDyDbHelper.isAllUploaded(historyTable.BaseInfoId))
                {
                    historyTable.IsLoad = 0;
                    historyTable.setLoadTime(0);
                }
            }
        }*/
        return historyTables;
    }


    public TreeMap<String, ArrayList<HistoryTable>> queryGroupPlanNum(long startTime, long endTime,int mode)
    {
        TreeMap<String,ArrayList<HistoryTable>> result = new TreeMap<>();
        String sql = "select SerialNo from History WHERE CreatTime < ? AND CreatTime > ? AND Type = ? group by SerialNo order by CreatTime DESC";
        Cursor cursor = mHistoryDb.getWritableDatabase().rawQuery(sql,new String[]{String.valueOf(startTime), String.valueOf(endTime),String.valueOf(mode)});
        if (cursor.moveToFirst())
        {
            do {
                String serilaNo = cursor.getString(cursor.getColumnIndex("SerialNo"));
                sql = "select  *from History WHERE CreatTime < ? AND CreatTime > ? AND SerialNo = ? AND Type = ? order by CreatTime DESC";
                Cursor historyCursor = mHistoryDb.getWritableDatabase().rawQuery(sql,new String[]{String.valueOf(startTime), String.valueOf(endTime),serilaNo,String.valueOf(mode)});
                if (historyCursor.moveToFirst()) {
                    ArrayList<HistoryTable> historyTables = new ArrayList<>();
                    do {
                        HistoryTable historyTable = new HistoryTable();
                        historyTable.setBaseInfoId(historyCursor.getString(historyCursor.getColumnIndex("BaseInfoId")));
                        historyTable.setCreatTime(historyCursor.getLong(historyCursor.getColumnIndex("CreatTime")));
                        historyTable.setIsLoad(historyCursor.getInt(historyCursor.getColumnIndex("IsLoad")));
                        historyTable.setPileNo(historyCursor.getString(historyCursor.getColumnIndex("PileNo")));
                        historyTable.setTestMode(historyCursor.getInt(historyCursor.getColumnIndex("Type")));
                        historyTable.setType(historyCursor.getInt(historyCursor.getColumnIndex("Type")));
                        historyTable.setSerialNo(historyCursor.getString(historyCursor.getColumnIndex("SerialNo")));
                        historyTable.setLoadTime(historyCursor.getLong(historyCursor.getColumnIndex("LoadTime")));
                        historyTable.setTestTime(historyCursor.getString(historyCursor.getColumnIndex("testTime")));
                        historyTables.add(historyTable);
                    }while (historyCursor.moveToNext());
                    historyCursor.close();
                    result.put(serilaNo,historyTables);
                }
            }while (cursor.moveToNext());
        }
        cursor.close();
        return result;
    }

    public TreeMap<String, ArrayList<HistoryTable>> queryGroupPlanNum(long startTime, long endTime)
    {
        TreeMap<String,ArrayList<HistoryTable>> result = new TreeMap<>();
        String sql = "select SerialNo from History WHERE CreatTime < ? AND CreatTime > ? group by SerialNo order by CreatTime DESC";
        Cursor cursor = mHistoryDb.getWritableDatabase().rawQuery(sql,new String[]{String.valueOf(startTime), String.valueOf(endTime)});
        if (cursor.moveToFirst())
        {
            do {
                String serilaNo = cursor.getString(cursor.getColumnIndex("SerialNo"));
                sql = "select  *from History WHERE CreatTime < ? AND CreatTime > ? AND SerialNo = ? order by CreatTime DESC";
                Cursor historyCursor = mHistoryDb.getWritableDatabase().rawQuery(sql,new String[]{String.valueOf(startTime), String.valueOf(endTime),serilaNo});
                if (historyCursor.moveToFirst()) {
                    ArrayList<HistoryTable> historyTables = new ArrayList<>();
                    do {
                        HistoryTable historyTable = new HistoryTable();
                        historyTable.setBaseInfoId(historyCursor.getString(historyCursor.getColumnIndex("BaseInfoId")));
                        historyTable.setCreatTime(historyCursor.getLong(historyCursor.getColumnIndex("CreatTime")));
                        historyTable.setIsLoad(historyCursor.getInt(historyCursor.getColumnIndex("IsLoad")));
                        historyTable.setPileNo(historyCursor.getString(historyCursor.getColumnIndex("PileNo")));
                        historyTable.setTestMode(historyCursor.getInt(historyCursor.getColumnIndex("Type")));
                        historyTable.setType(historyCursor.getInt(historyCursor.getColumnIndex("Type")));
                        historyTable.setSerialNo(historyCursor.getString(historyCursor.getColumnIndex("SerialNo")));
                        historyTable.setLoadTime(historyCursor.getLong(historyCursor.getColumnIndex("LoadTime")));
                        historyTable.setTestTime(historyCursor.getString(historyCursor.getColumnIndex("testTime")));
                        historyTables.add(historyTable);
                    }while (historyCursor.moveToNext());
                    historyCursor.close();
                    result.put(serilaNo,historyTables);
                }
            }while (cursor.moveToNext());
        }
        cursor.close();
        return result;
    }

    public void save(HistoryTable mHistoryTable)
    {
        QueryBuilder<HistoryTable> queryBuilder = new QueryBuilder<>(HistoryTable.class);
        queryBuilder.whereEquals("BaseInfoId",mHistoryTable.BaseInfoId);
        if (mHistoryDb.queryCount(queryBuilder) > 0)
        {
            if (!isUpload(mHistoryTable.BaseInfoId))
            {
                WhereBuilder whereBuilder = new WhereBuilder(HistoryTable.class);
                whereBuilder.where("BaseInfoId = ?",new Object[]{mHistoryTable.BaseInfoId});
                mHistoryDb.delete(whereBuilder);
                mHistoryDb.save(mHistoryTable);
            }
        }
        else
            mHistoryDb.save(mHistoryTable);
    }

    private boolean isUpload(String baseInfoId)
    {
        String sql = "select LoadTime from History where BaseInfoId = ?";
        Cursor cursor  = mHistoryDb.getWritableDatabase().rawQuery(sql,new String[]{baseInfoId});
        if (cursor.moveToNext())
        {
            long value = cursor.getLong(cursor.getColumnIndex("LoadTime"));
            cursor.close();
            return value > 0;
        }
        cursor.close();
        return false;
    }


    public ArrayList<HistoryTable> query()
    {
        return mHistoryDb.query(HistoryTable.class);
    }

    public List<HistoryTable> queryByBaseInfoId(String baseInfoId){
        QueryBuilder<HistoryTable> queryBuilder = new QueryBuilder<>(HistoryTable.class);
        queryBuilder.whereEquals("BaseInfoId",baseInfoId);
        return mHistoryDb.query(queryBuilder);
    }

    public long isExsitData(int day) {
        QueryBuilder<HistoryTable> queryBuilder =
                new QueryBuilder<>(HistoryTable.class);
        long endTime = new Date().getTime()-day* DbCenter.DAY_OF_TIME;
        queryBuilder.whereLessThan("CreatTime",endTime);
        return mHistoryDb.queryCount(queryBuilder);
    }

    public long isExsitJYDData(int remoteId)
    {
        QueryBuilder<HistoryTable> queryBuilder =
                new QueryBuilder<>(HistoryTable.class);
        queryBuilder.where("remoteId=?",remoteId);
        return mHistoryDb.queryCount(queryBuilder);
    }

    public void delete()
    {
        mHistoryDb.deleteAll(HistoryTable.class);
    }

    public void makeAsUpLoad(String BaseInfoId)
    {
        WhereBuilder whereBuilder = new WhereBuilder(HistoryTable.class);
        whereBuilder.andEquals("BaseInfoId",BaseInfoId);
        ColumnsValue columnsValue = new ColumnsValue(new String[]{"IsLoad","LoadTime"},new Object[]{1,new Date().getTime()});
        mHistoryDb.update(whereBuilder, columnsValue, ConflictAlgorithm.Fail);
    }

    public ArrayList<HistoryTable> queryBySerilNo(String inspectionFormSerilNo)
    {
        QueryBuilder<HistoryTable> queryBuilder = new QueryBuilder<>(HistoryTable.class);
        queryBuilder.whereEquals("SerialNo",inspectionFormSerilNo);
        ArrayList<HistoryTable> historyTables = mHistoryDb.query(queryBuilder);
        /*for (HistoryTable historyTable : historyTables)
        {
            if (historyTable.Type == HistoryTable.TEST_DY)
            {
                if (!DbCenter.mDyDbHelper.isAllUploaded(historyTable.BaseInfoId))
                {
                    historyTable.IsLoad = 0;
                    historyTable.setLoadTime(0);
                }
            }
        }*/
        return historyTables;
    }

    public ArrayList<CommitBean> queryIsUpLoadInPlan(String planNum,ArrayList<String> pileNo)
    {
        ArrayList<CommitBean> commitBeens = new ArrayList<>();
        for (String s : pileNo)
        {
            QueryBuilder<HistoryTable> queryBuilder = new QueryBuilder<>(HistoryTable.class);
            queryBuilder.whereEquals("PileNo",s);
            queryBuilder.whereAppendAnd();
            queryBuilder.whereEquals("SerialNo",planNum);
            CommitBean commitBean = new CommitBean();
            commitBean.setPileName(s);
            List<HistoryTable> tables = mHistoryDb.query(queryBuilder);
            if (tables!=null&&tables.size()>0)
            {
                if (tables.get(0).getIsLoad()==0) {
                    commitBean.setIsUpLoad(0);
                }
                else
                    commitBean.setIsUpLoad(1);
            }
            else
            {
                commitBean.setIsUpLoad(0);
            }
            commitBeens.add(commitBean);
        }

        return commitBeens;
    }

    @Override
    public void onUpdate(SQLiteDatabase sqLiteDatabase, int i, int i1) {
    }

    public int queryCount(String planNum)
    {
        QueryBuilder<HistoryTable> queryBuilder = new QueryBuilder<>(HistoryTable.class);
        queryBuilder.whereEquals("SerialNo",planNum);
        return (int)mHistoryDb.queryCount(queryBuilder);
    }

    public int queryTest(String upLoadBaseInfoId)
    {
        String sql = "select Type from History where BaseInfoId = ?";
        android.database.Cursor cursor = mHistoryDb.getWritableDatabase().rawQuery(sql,new String[]{upLoadBaseInfoId});
        if (cursor.moveToFirst())
        {
            int test = cursor.getInt(cursor.getColumnIndex("Type"));
            cursor.close();
            return test;
        }
        cursor.close();
        return -1;
    }


    public int queryLoadedCounts(String planNum)
    {
        QueryBuilder<HistoryTable> queryBuilder = new QueryBuilder<>(HistoryTable.class);
        queryBuilder.whereEquals("SerialNo",planNum);
        queryBuilder.whereAppendAnd();
        queryBuilder.whereGreaterThan("LoadTime",0);
        return (int)mHistoryDb.queryCount(queryBuilder);
    }


    public void makeUnUpLoad(String baseInfoId)
    {
        WhereBuilder whereBuilder = new WhereBuilder(HistoryTable.class);
        whereBuilder.andEquals("BaseInfoId",baseInfoId);
        ColumnsValue columnsValue = new ColumnsValue(new String[]{"IsLoad","LoadTime"},new Object[]{0,0});
        mHistoryDb.update(whereBuilder, columnsValue, ConflictAlgorithm.Fail);
    }
}
